package com.hiwan.system.util.excel;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.hiwan.platform.utils.StringUtils;


/**
 * 
 * @Description: Excel文件导入数据库的工具文件。
 * @Datetime   : 2015-3-12 上午8:40:22<br>
 * @Title      : ExcelTools.java<br>
 * @Company    : Hiwan<br>
 * @Author 	   : lsb<br>
 *
 */
public class ExcelTools<T> {
	private Class<T> clazz;
	private Workbook book;
	private Map<String, Field> fieldsMap;
	private boolean insertHeadRow = false;
	private String createUser;
	private String createTime;
	private String headName;
	
	public ExcelTools(Class<T> clazz, Workbook workbook) {
		this.clazz = clazz;
		this.book = workbook;
		this.fieldsMap = getClazzAllFields(clazz);
	}

	public ExcelTools(Class<T> clazz) {
		this.clazz = clazz;
		this.fieldsMap = getClazzAllFields(clazz);
	}

	public ExcelTools(Class<T> clazz,boolean insertHeadRow,String createUser,String createTime,String headName) {
		this.clazz = clazz;
		this.fieldsMap = getClazzAllFields(clazz);
		this.insertHeadRow = insertHeadRow;
		this.createUser=createUser;
		this.createTime = createTime;
		this.headName = headName;
	}
	
	public List<T> importExcel(Integer sheetId, Integer startRow, Integer startCol) throws InstantiationException,
			IllegalAccessException {
		return importExcel(sheetId, startRow, startCol, null);
	}

	/**
	 * 默认第一行是表头，故从第二行开始读取。
	 * @param sheetId
	 * @return
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public List<T> importExcel(Integer sheetId) throws InstantiationException, IllegalAccessException {
		return importExcel(sheetId, 1, 0, null);
	}

	/**
	 * 读取第一个sheet,从第2行开始。
	 * @return
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public List<T> importExcel() throws InstantiationException, IllegalAccessException {
		return importExcel(null, 1, 0, null);
	}

	/**
	 * 按sheet把excel中的数据封装成pojo对象
	 * @param sheetId  excel的sheetId，可为null，即只读第一个sheet页
	 * @param startRow 数据开始行
	 * @param startCol 数据开始列
	 * @param map  	      一些字段在excel没有对应的字段，可以通过map来添加，key是注解的字段，value是值	
	 * @return
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 */
	@SuppressWarnings("rawtypes")
	public List<T> importExcel(Integer sheetId, Integer startRow, Integer startCol, Map map)
			throws InstantiationException, IllegalAccessException {
		List<T> list = new ArrayList<T>();
		Sheet sheet = null;
		if (sheetId == null) {
			sheet = book.getSheetAt(0);
		} else {
			sheet = book.getSheetAt(sheetId);
		}
		// int rows = sheet.getLastRowNum();// 得到数据的行数
		int rows = sheet.getPhysicalNumberOfRows();
		if (rows > 0) {// 有数据时才处理
			// 定义一个map用于存放列的序号和field.
			for (int i = startRow; i < rows; i++) {// 从第start行开始取数据,默认第一行是表头
				Row row = sheet.getRow(i);// 得到一行中的所有单元格对象.
				// String cStr = "";//一行中的所有单元格中的对象拼成的字符串.
				T entity = clazz.newInstance();
				int cells = 0;
				if (row != null)
					cells = row.getLastCellNum();
				for (int j = startCol; j < cells; j++) {
					// System.out.println("第"+i+"行;第"+j+"列");
					String c = "";// 单元格中的内容.
					Cell cell = row.getCell(j);
					c = getCellStringValue(cell);
					Field field = fieldsMap.get(String.valueOf(j));// 从map中得到对应列的field.
					if (field == null) {
						continue;
					}

					entity = setFieldValue(entity, field, c);// 设置有注解属性的值
				}
				// 添加Excel中没有对应的字段属性
				addExtraField(entity, map);

				list.add(entity);

			}
		}
		return list;
	}

	/** 
	 *	
	    * 对list数据源将其里面的数据导入到excel表单 
	    * @param sheetName  工作表的名称 
	    * @param sheetSize  每个sheet中数据的行数,此数值必须小于65536 
	 */
	public boolean exportExcel(List<T> list, String sheetName, int sheetSize, OutputStream output) {
		return exportExcel(list, sheetName, sheetSize, output, "2003");
	}

	// TODO 导出格式 2003 2007
	public boolean exportExcel(List<T> list, String sheetName, int sheetSize, OutputStream output, String version) {
		Workbook workbook = null;
		Field[] allFields = clazz.getDeclaredFields();// 得到所有定义字段
		List<Field> fields = new ArrayList<Field>();
		
		if (StringUtils.equals(version, "2003")) {
			workbook = new HSSFWorkbook();// 产生工作薄对象
		} else {
			workbook = new XSSFWorkbook(); // 产生工作薄对象
		}
		// 得到所有field并存放到一个list中.
		for (Field field : allFields) {
			if (field.isAnnotationPresent(ExcelVOAttribute.class)) {
				fields.add(field);
			}
		}

		HSSFCellStyle style = (HSSFCellStyle) workbook.createCellStyle();
		style.setFillForegroundColor(HSSFColor.YELLOW.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直    
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		HSSFFont titlefont=(HSSFFont) workbook.createFont();
	    titlefont.setFontHeightInPoints((short)14);
	    titlefont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);         //字体增粗
	    style.setFont(titlefont);
		
	    HSSFCellStyle headStyle = (HSSFCellStyle) workbook.createCellStyle();
	    headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直    
	    headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
	    HSSFFont font=(HSSFFont) workbook.createFont();
        font.setFontHeightInPoints((short)26);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);         //字体增粗
        headStyle.setFont(font);
        
        HSSFCellStyle valueStyle = (HSSFCellStyle) workbook.createCellStyle();
        valueStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直    
        valueStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
        valueStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        valueStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        valueStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        valueStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        
		// excel2003中每个sheet中最多有65536行,为避免产生错误所以加这个逻辑.
		if (sheetSize > 65536 || sheetSize < 1) {
			sheetSize = 65536;
		}
		double sheetNo = Math.ceil(list.size() / sheetSize);// 取出一共有多少个sheet.
		try {
			for (int index = 0; index <= sheetNo; index++) {
				Sheet sheet = workbook.createSheet();// 产生工作表对象
				if (sheetNo == 0) {
					workbook.setSheetName(index, sheetName);
				} else {
					workbook.setSheetName(index, sheetName + index);// 设置工作表的名称.
				}
				Row row;
				Cell cell;// 产生单元格
				
				row = sheet.createRow(0);// 产生一行
				Map columnLength = new HashMap();
				// 写入各个字段的列头名称
				for (int i = 0; i < fields.size(); i++) {
					Field field = fields.get(i);
					ExcelVOAttribute attr = field.getAnnotation(ExcelVOAttribute.class);
					int col = getExcelCol(attr.column());// 获得列号
					cell = row.createCell(col);// 创建列
					cell.setCellType(Cell.CELL_TYPE_STRING);// 设置列中写入内容为String类型
					cell.setCellValue(attr.name());// 写入列名
					cell.setCellStyle(style);
					columnLength.put(i,attr.name().getBytes().length*2);
					// 如果设置了提示信息则鼠标放上去提示.
					if (StringUtils.isNotEmpty(attr.prompt().trim())) {
						setHSSFPrompt(sheet, "", attr.prompt(), 1, 100, col, col);// 这里默认设了2-101列提示.
					}
					// 如果设置了combo属性则本列只能选择不能输入
					if (attr.combo().length > 0) {
						setHSSFValidation(sheet, attr.combo(), 1, 100, col, col);// 这里默认设了2-101列只能选择不能输入.
					}
				}

				int startNo = index * sheetSize;
				int endNo = Math.min(startNo + sheetSize, list.size());

				// 写入各条记录,每条记录对应excel表中的一行
				for (int i = startNo; i < endNo; i++) {
					row = sheet.createRow(i + 1 - startNo);
					
					T vo = (T) list.get(i); // 得到导出对象.
					for (int j = 0; j < fields.size(); j++) {
						Field field = fields.get(j);// 获得field.
						field.setAccessible(true);// 设置实体类私有属性可访问
						ExcelVOAttribute attr = field.getAnnotation(ExcelVOAttribute.class);

						// 根据ExcelVOAttribute中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
						if (attr.isExport()) {
							cell = row.createCell(getExcelCol(attr.column()));// 创建cell
							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
							String cellValue = field.get(vo) == null ? "" : String.valueOf(field.get(vo));
							cell.setCellValue(cellValue);// 如果数据存在就填入,不存在填入空格.
							cell.setCellStyle(valueStyle);
							int length = (int)columnLength.get(j);
							if(cellValue.getBytes().length > length){
								columnLength.put(j, cellValue.getBytes().length);
							}
						}
					}
				}
				Set keySet = columnLength.keySet();
				Iterator iter = keySet.iterator();
				while(iter.hasNext()){
					int key = (int)iter.next();
					sheet.setColumnWidth(key, (int)columnLength.get(key)*256);
				}
				if(insertHeadRow){
					sheet.shiftRows(sheet.getFirstRowNum()-1, sheet.getLastRowNum(), 2, true, false);
					Row insertRow = sheet.createRow(0);
					sheet.addMergedRegion(new CellRangeAddress(0,(short)0,0,(short) fields.size()-1));
					Cell insertCell = insertRow.createCell(0);// 创建列
					insertCell.setCellValue(headName);
					insertCell.setCellStyle(headStyle);
					insertRow = sheet.createRow(1);
					insertCell = insertRow.createCell(3);
					insertCell.setCellValue("制表人：");
					insertCell = insertRow.createCell(4);
					insertCell.setCellValue(createUser);
					insertCell = insertRow.createCell(5);
					insertCell.setCellValue("制表日期：");
					insertCell = insertRow.createCell(6);
					insertCell.setCellValue(createTime);
				}
			}
			output.flush();
			workbook.write(output);
			return true;
		} catch (IllegalArgumentException e) {
			throw new RuntimeException(e);
		} catch (IllegalAccessException e) {
			throw new RuntimeException(e);
		} catch (IOException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				output.close();
				workbook.close();
			} catch (IOException e) {
				throw new RuntimeException(e);
			}
		}
	}

	private void ttt(){
		
	}
	
	/** 
	 * 设置单元格上提示 
	 *  
	 * @param sheet 
	 *            要设置的sheet. 
	 * @param promptTitle 
	 *            标题 
	 * @param promptContent 
	 *            内容 
	 * @param firstRow 
	 *            开始行 
	 * @param endRow 
	 *            结束行 
	 * @param firstCol 
	 *            开始列 
	 * @param endCol 
	 *            结束列 
	 * @return 设置好的sheet. 
	 */
	public static Sheet setHSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
			int firstCol, int endCol) {
		// 构造constraint对象
		DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("DD1");
		// 四个参数分别是：起始行、终止行、起始列、终止列
		CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
		// 数据有效性对象
		HSSFDataValidation data_validation_view = new HSSFDataValidation(regions, constraint);
		data_validation_view.createPromptBox(promptTitle, promptContent);
		sheet.addValidationData(data_validation_view);
		return sheet;
	}

	/** 
	 * 设置某些列的值只能输入预制的数据,显示下拉框. 
	 *  
	 * @param sheet  要设置的sheet. 
	 * @param textlist 下拉框显示的内容 
	 * @param firstRow   开始行 
	 * @param endRow 结束行 
	 * @param firstCol 开始列 
	 * @param endCol  结束列 
	 * @return 设置好的sheet. 
	 */
	public static Sheet setHSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol,
			int endCol) {
		// 加载下拉列表内容
		DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);
		// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
		CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
		// 数据有效性对象
		HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
		sheet.addValidationData(data_validation_list);
		return sheet;
	}

	/**
	 * @return 
	 * 
	* @Description: 添加额外的属性值
	* @param    map,key为属性名，value为属性值
	* @return void 
	* @throws
	 */
	@SuppressWarnings("rawtypes")
	private void addExtraField(T entity, Map map) {
		if (map == null) {// 如果没有额外属性需要添加,
			return;
		}
		Iterator i = map.entrySet().iterator();
		while (i.hasNext()) {// 只遍历一次,速度快
			Map.Entry e = (Map.Entry) i.next();
			String key = (String) e.getKey();
			String value = (String) e.getValue();
			Field field = fieldsMap.get(key);

			setFieldValue(entity, field, value);
		}
	}

	/**
	 * 
	* @Description: 给指定对象的属性赋值
	* @param    entity,要赋值的对象
	* @param    field,对象中要赋值的属性
	* @param    c,属性值
	* @return 	T 
	* @throws
	 */
	public T setFieldValue(T entity, Field field, String value) {
		// T entity=null;
		try {
			// entity = clazz.newInstance();
			Class<?> fieldType = field.getType();
			if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
				field.set(entity, Integer.parseInt(value));
			} else if (String.class == fieldType) {
				field.set(entity, String.valueOf(value));
			} else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
				field.set(entity, Long.valueOf(value));
			} else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
				field.set(entity, Float.valueOf(value));
			} else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
				field.set(entity, Short.valueOf(value));
			} else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
				field.set(entity, Double.valueOf(value));
			} else if (Character.TYPE == fieldType) {
				if ((value != null) && (value.length() > 0)) {
					field.set(entity, Character.valueOf(value.charAt(0)));
				}
			}

		} catch (NumberFormatException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}

		return entity;
	}

	/**
	 * 返回一个Class类型的解析Map
	 * @param clazz
	 * @return
	 */
	public Map<String, Field> getClazzAllFields(Class<T> clazz) {
		Field[] allFields = clazz.getDeclaredFields();// 得到类的所有field.
		Map<String, Field> fieldsMap = new HashMap<String, Field>();// 定义一个map用于存放列的序号和field.
		for (Field field : allFields) {
			if (field.isAnnotationPresent(ExcelVOAttribute.class)) {
				ExcelVOAttribute attr = field.getAnnotation(ExcelVOAttribute.class);
				String colChar = attr.column();
				// String dict=attr.dict();
				if (colChar.startsWith("set")) {// 如果此列的注释是以set开始，map中的key就存注解中column中的值
					fieldsMap.put(colChar, field);
				}
				String colSort = String.valueOf(getExcelCol(colChar));// 获得列号
				field.setAccessible(true);// 设置类的私有字段属性可访问.
				fieldsMap.put(colSort, field);
			}
		}
		return fieldsMap;
	}

	/**
	 * 将EXCEL中A,B,C,D,E列映射成0,1,2,3
	 * 
	 * @param col
	 */
	public static int getExcelCol(String col) {
		col = col.toUpperCase();
		// 从-1开始计算,字母重1开始运算。这种总数下来算数正好相同。
		int count = -1;
		char[] cs = col.toCharArray();
		for (int i = 0; i < cs.length; i++) {
			count += (cs[i] - 64) * Math.pow(26, cs.length - 1 - i);
		}
		return count;
	}

	/**
	 * 取出单元格String类型的值
	 * @param cell
	 * @return
	 */
	public static String getCellStringValue(Cell cell) {
		String cellValue = "";
		String dateFormat = "yyyy-MM-dd";
		if (cell == null) {
			return cellValue;
		}
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:// 字符串类型
			cellValue = cell.getStringCellValue().toString();
			if (cellValue.trim().equals("") || cellValue.trim().length() <= 0)
				cellValue = "";
			break;
		case Cell.CELL_TYPE_NUMERIC: // 数值类型(包含日期)
			if (DateUtil.isCellDateFormatted(cell)) {
				Date date = cell.getDateCellValue();
				cellValue = new SimpleDateFormat(dateFormat).format(date);
			} else {
				cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_FORMULA: // 公式

			try {
				cellValue = String.valueOf(cell.getStringCellValue());
			} catch (IllegalStateException e) {
				cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_BLANK:
			cellValue = "";
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			break;
		case Cell.CELL_TYPE_ERROR:
			break;
		default:
			cellValue = "";
			break;
		}
		return cellValue.trim();
	}
}
